﻿/******************************************************************************
'Description:报表通用方法类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2017-11-17
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Web;
using System.Configuration;
namespace ReportBusiness
{
    public class uMESCommonMethod
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();

        //剔除工序名称中的件号
        public string GetSpecNameWithOutProdName(string strSpecName)
        {
            string[] array = strSpecName.Split('-');
            int intLen = array.Length;

            string str = "";
            if (intLen >= 3)
            {
                str = array[intLen - 2].ToString() + "-" + array[intLen - 1].ToString();
            }
            else
            {
                str = strSpecName;
            }

            return str;
        }

        //剔除批次名称中的件号
        public string ReplaceProductNameForContainer(string strResource)
        {
            string[] strArray = strResource.Split('/');
            if (strArray.Length >= 2)
            {
                return strArray[1];
            }
            else
            {
                return strResource;
            }
        }

        //获取机型
        public DataTable GetProductFamily()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select pf.productfamilyname from productfamily pf order by pf.productfamilyname");
            return dal.GetDataTable(sb.ToString());
        }

        //查询所有车间
        public DataTable GetAllFactory()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT f.factoryid,f.factoryname");
            sb.AppendLine("FROM factory f");
            sb.AppendLine("LEFT JOIN Userattribute ua ON f.factoryid = ua.parentid AND ua.userattributename = '分厂代码'");
            sb.AppendLine("WHERE ua.attributevalue IS NOT NULL ");
            sb.AppendLine(" AND ua.attributevalue IN ('601','609','600','607') ");//
            sb.AppendLine("ORDER BY f.factoryname ");
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        //查询所有车间
        public DataTable GetAllFactory2()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT f.factoryid,f.factoryname");
            sb.AppendLine("FROM factory f");
            sb.AppendLine("LEFT JOIN Userattribute ua ON f.factoryid = ua.parentid AND ua.userattributename = '分厂代码'");
            sb.AppendLine("WHERE ua.attributevalue IS NOT NULL ");
            sb.AppendLine(" AND ua.attributevalue IN ('601','609','600','607','603') ");//
            sb.AppendLine("ORDER BY f.factoryname ");
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        //查询分厂
        public DataTable GetFactory(Dictionary<string, string> para)
        {

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT f.factoryname,f.factoryid");
            sb.AppendLine("FROM factory f");
            sb.AppendLine("WHERE 1=1 ");
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            sb.AppendLine("ORDER BY f.factoryname");
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        //查询工区所属分厂
        public DataTable GetFactoryByWorkcenterName(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT f.factoryname,f.factoryid");
            sb.AppendLine("FROM factory f");
            sb.AppendLine("LEFT JOIN workcenter w ON w.factoryid = f.factoryid ");
            sb.AppendLine("where 1=1 ");
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 查询所有工区
        public DataTable GetAllWorkcenter()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT f.factoryname,w.workcentername,w.workcenterid");
            sb.AppendLine("FROM workcenter w");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = w.factoryid ");
            sb.AppendLine("LEFT JOIN Userattribute ua ON f.factoryid = ua.parentid AND ua.userattributename = '分厂代码'");
            sb.AppendLine("WHERE w.wctype IS NULL AND w.notes IS NULL AND ua.attributevalue IS NOT NULL AND ua.attributevalue IN ('601','609','600','607') ");
            sb.AppendLine("ORDER BY f.factoryname,w.workcentername");
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 查询工区
        public DataTable GetWorkcenter(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT w.workcenterid,w.workcentername");
            sb.AppendLine("FROM factory f");
            sb.AppendLine("LEFT JOIN workcenter w ON w.factoryid = f.factoryid");
            sb.AppendLine("WHERE w.notes IS NULL ");
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            sb.AppendLine("ORDER BY w.workcentername");

            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 根据factoryname查询工区
        public DataTable GetWorkcenterByFactory(string factoryname)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" select w.workcentername,w.workcenterid from workcenter w");
            sb.AppendLine(" left join factory f on f.factoryid=w.factoryid where w.notes is null ");
            if (factoryname != "全部")
            {
                sb.AppendFormat(" AND f.factoryname='{0}'", factoryname);
            }
            return dal.GetDataTable(sb.ToString());
        }

        // 根据factoryname查询生产工区
        public DataTable GetManuWorkcenterByFactory(string factoryname)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" select w.workcentername,w.workcenterid from workcenter w");
            sb.AppendLine(" left join factory f on f.factoryid=w.factoryid where w.notes is null ");
            if (factoryname != "全部")
            {
                sb.AppendFormat(" AND w.wctype IS NULL AND f.factoryname='{0}'", factoryname);
            }
            return dal.GetDataTable(sb.ToString());
        }

        // 查询班组  (分厂-工区-班组)
        public DataTable GetTeam(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT t.teamid,t.teamname");
            sb.AppendLine("FROM team t");
            sb.AppendLine("LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = w.factoryid");
            sb.AppendLine("WHERE 1=1 ");
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname = '{0}'", para["teamname"]);
                }
            }
            if (para.ContainsKey("teamid"))
            {
                if (para["teamid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamid = '{0}'", para["teamid"]);
                }
            }
            sb.AppendLine("ORDER BY t.teamname");
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 根据Workcenterid查询班组
        public DataTable GetTeamByWorkcenterID(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" select t.teamname,t.teamid from team t ");
            sb.AppendLine(" left join workcenter wc on wc.workcenterid=t.workcenterid");
            sb.AppendLine(" left join factory f on f.factoryid = wc.factoryid ");
            sb.AppendLine(" where 1=1");
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" and wc.workcenterid ='{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" and wc.workcentername ='{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" and f.factoryid = '{0}'", para["factoryid"]);
            }
            sb.AppendLine(" order by t.teamname ");
            return dal.GetDataTable(sb.ToString());
        }

        // 根据Workcentername查询班组
        public DataTable GetTeamByWorkcentername(string workCenter)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" select t.teamname,t.teamid from team t ");
            sb.AppendLine(" left join workcenter wc on wc.workcenterid=t.workcenterid");
            sb.AppendLine(" where 1=1");
            if (workCenter != "全部")
            {
                sb.AppendFormat(" and wc.workcentername='{0}'", workCenter);
            }
            sb.AppendLine(" order by t.teamname ");
            return dal.GetDataTable(sb.ToString());
        }

        // 查询所有工种
        public DataTable GetOperationname()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" select distinct o.operationname,o.operationid from operation o order by o.operationname ");
            return dal.GetDataTable(sb.ToString());
        }

        //查询所有ResoureType
        public DataTable GetAllResoureType()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(" SELECT rt.resourcetypename,rt.resourcetypeid");
            sb.AppendLine(" FROM resourcetype rt");
            return dal.GetDataTable(sb.ToString());
        }

        // 查询登录人角色 add zhunan 2018.4.3
        public string GetEmployeeRole(string employeeid)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT rd.rolename FROM employee e");
            sb.AppendLine("LEFT JOIN employeerole er ON er.employeeid = e.employeeid");
            sb.AppendLine("LEFT JOIN roledef rd ON rd.roleid = er.roleid");
            sb.AppendFormat("WHERE e.employeeid = '{0}'", employeeid);

            DataTable dtRole = dal.GetDataTable(sb.ToString());

            int isWorker = Convert.ToInt32(dtRole.Compute("Count(rolename)", "rolename = '工人'"));
            int isTeanManager = Convert.ToInt32(dtRole.Compute("Count(rolename)", "rolename = '班组长'"));
            int isTeamManager = Convert.ToInt32(dtRole.Compute("Count(rolename)", "rolename = '组长'"));
            int isShiftManager = Convert.ToInt32(dtRole.Compute("Count(rolename)", "rolename = '班长'"));
            int isWorkcenterManager = Convert.ToInt32(dtRole.Compute("Count(rolename)", "rolename = '工区长'"));
            int isDingEYuan = Convert.ToInt32(dtRole.Compute("Count(rolename)", "rolename = '定额员'"));

            string role = "";

            if (isDingEYuan == 1)
            {
                role = "定额员";
                return role;
            }

            if (isWorkcenterManager == 1)
            {
                role = "工区长";
                return role;
            }

            if (isTeanManager == 1)
            {
                role = "班组长";
                return role;
            }

            if (isTeamManager == 1)
            {
                role = "组长";
                return role;
            }

            if (isShiftManager == 1)
            {
                role = "班长";
                return role;
            }

            if (isWorker == 1)
            {
                role = "工人";
                return role;
            }
            return "";
        }

        // 根据工区获取员工
        public DataTable GetEmp(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select distinct e.fullname, e.description,e.employeeid from employee e  ");
            sb.AppendLine("left join sessionvalues sv on sv.sessionvaluesid = e.sessionvaluesid ");
            sb.AppendLine("left join workcenter wc on wc.workcenterid = sv.workcenterid");
            sb.AppendLine("where 1=1 ");
            if (para.ContainsKey("WorkCt"))
            {
                if (para["WorkCt"] != "全部")
                {
                    sb.AppendFormat(" and wc.workcentername='{0}'", para["WorkCt"]);
                }
            }
            sb.AppendLine(" order by e.description ");
            DataTable EmpDt = dal.GetDataTable(sb.ToString());
            return EmpDt;
        }

        // 根据班组获取员工
        public DataTable GetEmpByTeam(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            //todo litan
            sb.AppendLine(" select distinct e.description,e.employeeid,e.fullname from employee e ");
            sb.AppendLine(" left join teamemployee te on te.employeeid = e.employeeid");
            sb.AppendLine(" left join team t on t.teamid = te.teamid ");
            sb.AppendLine(" left join sessionvalues sv ON sv.employeeid = e.employeeid ");
            sb.AppendLine(" left join workcenter wc on wc.workcenterid = sv.workcenterid");
            sb.AppendLine(" left join factory f on f.factoryid = sv.factoryid ");
            sb.AppendLine(" where 1=1 ");
            if (para.ContainsKey("TEAMID"))
            {
                if (para["TEAMID"] != "全部")
                {
                    sb.AppendFormat(" and t.teamid='{0}'", para["TEAMID"]);
                }
            }
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"] != "全部")
                {
                    sb.AppendFormat(" and t.teamname='{0}'", para["teamname"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" and wc.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" and wc.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "")
                {
                    sb.AppendFormat(" and f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat(" and f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            sb.AppendLine(" order by e.description ");
            DataTable EmpDt = dal.GetDataTable(sb.ToString());
            return EmpDt;
        }

        //根据班组获取员工
        public DataTable GetEmployeeByTeam(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            //todo litan
            sb.AppendLine(" select distinct e.description,e.employeeid,e.fullname from employee e ");
            sb.AppendLine(" left join teamemployee te on te.employeeid = e.employeeid");
            sb.AppendLine(" left join team t on t.teamid = te.teamid ");
            sb.AppendLine(" left join sessionvalues sv ON sv.employeeid = e.employeeid ");
            sb.AppendLine(" left join workcenter wc on wc.workcenterid = sv.workcenterid");
            sb.AppendLine(" left join factory f on f.factoryid = sv.factoryid ");
            sb.AppendLine(" where 1=1 ");
            if (para.ContainsKey("TEAMID"))
            {
                if (para["TEAMID"] != "全部")
                {
                    sb.AppendFormat(" and t.teamid='{0}'", para["TEAMID"]);
                }
            }
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"] != "全部")
                {
                    sb.AppendFormat(" and t.teamname='{0}'", para["teamname"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" and wc.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" and wc.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "")
                {
                    sb.AppendFormat(" and f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat(" and f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            sb.AppendLine(" order by e.description ");
            DataTable EmpDt = dal.GetDataTable(sb.ToString());
            return EmpDt;
        }

        // 查询员工(分厂、工区、工区、班组、工种)
        public DataTable GetEmployee(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT DISTINCT e.employeeid,e.employeename,e.fullname");
            sb.AppendLine("FROM employee e");
            sb.AppendLine("LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid");
            sb.AppendLine("LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = sv.factoryid");
            sb.AppendLine("LEFT JOIN teamemployee te ON te.employeeid = e.employeeid");
            sb.AppendLine("LEFT JOIN team t ON t.teamid = te.teamid");
            sb.AppendLine("LEFT JOIN operation o ON o.operationid=sv.operationid");
            sb.AppendLine("WHERE 1=1 ");
            if (para.ContainsKey("operation"))
            {
                if (para["operation"] != "全部")
                {
                    sb.AppendFormat(" AND o.operationname='{0}'", para["operation"]);
                }
            }
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname = '{0}'", para["teamname"]);
                }
            }
            if (para.ContainsKey("teamid"))
            {
                if (para["teamid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamid = '{0}'", para["teamid"]);
                }
            }
            if (para.ContainsKey("employeename"))
            {
                if (para["employeename"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND e.employeename = '{0}'", para["employeename"]);
                }
            }
            if (para.ContainsKey("fullname"))
            {
                if (para["fullname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND e.fullname = '{0}'", para["fullname"]);
                }
            }
            if (para.ContainsKey("employeeid"))
            {
                if (para["employeeid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND e.employeeid = '{0}'", para["employeeid"]);
                }
            }
            sb.AppendLine("ORDER BY e.employeename");

            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 查询同一车间内角色为 "计划员" 的所有员工 add by zhunan on 2017.08.25
        public DataTable getEmployeeCanStartContainer(string factoryname)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select e.employeename,e.fullname,e.employeeid");
            sb.AppendLine("from roledef rd");
            sb.AppendLine("left join employeerole er on er.roleid = rd.roleid");
            sb.AppendLine("left join employee e on e.employeeid = er.employeeid");
            sb.AppendLine("left join sessionvalues sv on sv.sessionvaluesid = e.sessionvaluesid");
            sb.AppendLine("left join factory f on f.factoryid = sv.factoryid");
            sb.AppendLine("where rd.rolename = '计划员' ");
            if (!string.IsNullOrEmpty(factoryname))
            {
                sb.AppendFormat(" and f.factoryname='{0}'", factoryname);
            }
            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 查询同一车间内角色为 "调度员" 的所有员工 add by zhunan on 2018.07.2
        public DataTable getFactoryAllDiaoDuRoleEmployee(string factoryname)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select e.employeename,e.fullname,e.employeeid");
            sb.AppendLine("from roledef rd");
            sb.AppendLine("left join employeerole er on er.roleid = rd.roleid");
            sb.AppendLine("left join employee e on e.employeeid = er.employeeid");
            sb.AppendLine("left join sessionvalues sv on sv.sessionvaluesid = e.sessionvaluesid");
            sb.AppendLine("left join factory f on f.factoryid = sv.factoryid");
            sb.AppendLine("where rd.rolename = '调度员' ");
            if (!string.IsNullOrEmpty(factoryname))
            {
                sb.AppendFormat(" and f.factoryname='{0}'", factoryname);
            }
            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 筛选角色为"检验员"的员工
        public DataTable GetEmployeeCanCheck(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT e.employeename,e.employeeid,e.fullname");
            sb.AppendLine("FROM roledef rd");
            sb.AppendLine("LEFT JOIN employeerole er ON er.roleid = rd.roleid");
            sb.AppendLine("LEFT JOIN employee e ON e.employeeid = er.employeeid");
            sb.AppendLine("LEFT JOIN teamemployee te ON te.employeeid = e.employeeid");
            sb.AppendLine("LEFT JOIN team t ON t.teamid = te.teamid");
            sb.AppendLine("LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid");
            sb.AppendLine("LEFT JOIN workcenter w ON w.workcenterid = sv.workcenterid");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = sv.factoryid");
            sb.AppendLine("WHERE rd.rolename = '检验员'");
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "")
                {
                    sb.AppendFormat(" AND sv.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("teamid"))
            {
                if (para["teamid"].ToString() != "")
                {
                    sb.AppendFormat(" AND t.teamid = '{0}'", para["teamid"]);
                }
            }
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname = '{0}'", para["teamname"]);
                }
            }
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 查找可派工员工
        public DataTable GetEmployeeCanDispatch(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT DISTINCT e.employeename,e.employeeid,e.fullname");
            sb.AppendLine("FROM roledef rd");
            sb.AppendLine("LEFT JOIN employeerole er ON er.roleid = rd.roleid");
            sb.AppendLine("LEFT JOIN employee e ON e.employeeid = er.employeeid");
            sb.AppendLine("LEFT JOIN teamemployee te ON te.employeeid = e.employeeid");
            sb.AppendLine("LEFT JOIN team t ON t.teamid = te.teamid");
            sb.AppendLine("LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid");
            sb.AppendLine("LEFT JOIN workcenter w ON w.workcenterid = sv.workcenterid");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = sv.factoryid");
            sb.AppendLine("WHERE rd.rolename IN ('班组长','工区长','调度员') ");
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "")
                {
                    sb.AppendFormat(" AND sv.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("teamid"))
            {
                if (para["teamid"].ToString() != "")
                {
                    sb.AppendFormat(" AND t.teamid = '{0}'", para["teamid"]);
                }
            }
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname = '{0}'", para["teamname"]);
                }
            }
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        //根据零件图号获取工序名
        public DataTable GetSpec(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT distinct sb.specbaseid,sb.specname,ws.workflowstepid,ws.workflowstepname FROM productbase pb");
            sb.AppendLine("LEFT JOIN product p ON pb.productbaseid=p.productbaseid");
            sb.AppendLine("LEFT JOIN Workflow w ON p.workflowid=w.workflowid OR p.workflowbaseid = w.workflowbaseid");
            sb.AppendLine("LEFT JOIN Workflowbase wb ON w.workflowbaseid=wb.workflowbaseid");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON w.workflowid=ws.workflowid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid=ws.specid or s.specbaseid=ws.specbaseid");
            sb.AppendLine("LEFT JOIN specbase sb ON s.specbaseid=sb.specbaseid");
            sb.AppendLine("where 1 = 1");
            if (para.ContainsKey("Prd"))
            {
                if (para["Prd"] != "全部")
                {
                    sb.AppendFormat(" and pb.productname='{0}'", para["Prd"]);
                }
            }
            if (para.ContainsKey("productname"))
            {
                if (para["productname"] != "全部")
                {
                    sb.AppendFormat(" and pb.productname='{0}'", para["productname"]);
                }
            }
            sb.AppendLine(" ORDER BY sb.specname ");
            DataTable SpecDt = dal.GetDataTable(sb.ToString());
            return SpecDt;
        }

        // 根据工种查询员工
        public DataTable GetEmpByOpr(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            string sql = " select distinct e.fullname,e.employeename,e.description,e.employeeid" +
                         " from employee e" +
                         " left join sessionvalues sv on sv.employeeid = e.employeeid" +
                         " left join operation o on o.operationid = sv.operationid" +
                         " left join workcenter w on w.workcenterid = sv.workcenterid" +
                         " left join factory f on sv.factoryid = f.factoryid" +
                         " left join teamemployee te on te.employeeid = e.employeeid" +
                         " left join team t on t.teamid = te.teamid" +
                         " where 1=1 ";
            if (para.ContainsKey("operationname"))
            {
                sb.AppendFormat(" and o.operationname='{0}'", para["operationname"]);
            }
            if (para.ContainsKey("factoryname"))
            {
                sb.AppendFormat(" and f.factoryname='{0}'", para["factoryname"]);
            }
            if (para.ContainsKey("workcentername"))
            {
                sb.AppendFormat(" and w.workcentername='{0}'", para["workcentername"]);
            }
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"] != "全部")
                {
                    sb.AppendFormat(" and t.teamname='{0}'", para["teamname"]);
                }
            }
            sql += sb.ToString();
            DataTable dtEmployee = dal.GetDataTable(sql);
            return dtEmployee;
        }

        // 查询图号查询工艺流程上所有工序
        public DataTable GetSpecByProduct(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT distinct sb.specbaseid,sb.specname,ws.workflowstepid,ws.workflowstepname FROM productbase pb");
            sb.AppendLine("LEFT JOIN product p ON pb.productbaseid=p.productbaseid");
            sb.AppendLine("LEFT JOIN Workflow w ON p.workflowid=w.workflowid OR p.workflowbaseid = w.workflowbaseid");
            sb.AppendLine("LEFT JOIN Workflowbase wb ON w.workflowbaseid=wb.workflowbaseid");
            sb.AppendLine("LEFT JOIN Workflowstep ws ON w.workflowid=ws.workflowid");
            sb.AppendLine("LEFT JOIN spec s ON s.specid=ws.specid or s.specbaseid=ws.specbaseid");
            sb.AppendLine("LEFT JOIN specbase sb ON s.specbaseid=sb.specbaseid");
            sb.AppendLine("where 1 = 1");
            if (para.ContainsKey("productname"))
            {
                if (para["productname"] != "全部")
                {
                    sb.AppendFormat(" and pb.productname='{0}'", para["productname"]);
                }
            }
            sb.AppendLine(" ORDER BY sb.specname ");
            DataTable SpecDt = dal.GetDataTable(sb.ToString());
            return SpecDt;
        }

        // 遍历分页之后的查询结果，拼接出字符串作为其他查询的过滤条件 add by zhunan on 2017-11-20
        public string getContainerList(DataTable dt,string productname = "", string togetherflag = "",string productfamily = "",string mfgordername = "")
        {
            StringBuilder sb = new StringBuilder();
            if (dt.Rows.Count > 0)
            {
                sb.Append("(");
                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("(");
                    if (productname != "")
                    {
                        sb.AppendFormat(productname + "='{0}' ", dr["productname"]);
                    }
                    if (togetherflag != "")
                    {
                        sb.AppendFormat("AND " + togetherflag + "='{0}'", dr["togetherflag"]);
                    }
                    if (productfamily != "")
                    {
                        sb.AppendFormat("AND " + productfamily + "='{0}'", dr["productfamily"]);
                    }
                    if (mfgordername != "")
                    {
                        sb.AppendFormat("AND " + mfgordername + "='{0}'", dr["mfgordername"]);
                    }
                    sb.Append(") OR ");
                }
                sb.Remove(sb.Length-3,3);
                sb.Append(")");
                return sb.ToString();
            }
            else
            {
                return "(1=1)";
            }
            
        }

        // 查询开卡数（根据机型、图号分组） add by zhunan on 2017-11-20
        public DataTable GetProductKaikaQty(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT trim(replace(SUBSTR(c.mfgbatch,4),'|','')) productfamily,SUM(c.originalqty) kaikaQty,pb.productname");
            sb.AppendLine("FROM container c");
            sb.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            sb.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            sb.AppendLine("WHERE c.containername <> c.containerid AND c.parentcontainerid IS NULL AND c.splitfromid IS NULL AND " + containerList + "");
            sb.AppendLine("GROUP BY c.mfgbatch,pb.productname");

            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 查询开卡数（根据机型、图号、批次分组） add by zhunan on 2017-11-20
        public DataTable GetProductKaikaQtyByTogetherflag(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT trim(replace(SUBSTR(c.mfgbatch,4),'|','')) productfamily,SUM(c.originalqty) kaikaQty,pb.productname,c.togetherflag,mfg.mfgordername");
            sb.AppendLine("FROM container c");
            sb.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            sb.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            sb.AppendLine("LEFT JOIN mfgorder mfg ON mfg.mfgorderid = c.mfgorderid");
            sb.AppendLine("WHERE c.containername <> c.containerid AND c.parentcontainerid IS NULL AND c.splitfromid IS NULL AND " + containerList + "");
            sb.AppendLine("GROUP BY c.mfgbatch,pb.productname,c.togetherflag,mfg.mfgordername");

            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 查询报废数（根据机型、图号分组） add by zhunan on 2017-11-20
        public DataTable GetProductScrapQty(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT M.productfamily,M.productname,SUM(M.scrapQty) scrapQty");
            sb.AppendLine("FROM (");
            // 加工过程中产生的报废数
            sb.AppendLine("       SELECT TRIM(replace(substr(c.mfgbatch,4),'|','')) productfamily,csr.productname,SUM(NVL(csr.lossqty,0)) scrapQty");
            sb.AppendLine("       FROM container c");
            sb.AppendLine("       LEFT JOIN containersteprecord csr ON c.containerid = csr.containerid");
            sb.AppendLine("       WHERE csr.lossqty > 0 AND " + containerList + "");
            sb.AppendLine("       GROUP BY c.mfgbatch,csr.productname");
            //转工报废数                      
            sb.AppendLine("       UNION ALL ");
            sb.AppendLine("       SELECT trim(csr.familylistnames) productfamily,csr.productname,SUM(cr.lossqty) scrapQty");
            sb.AppendLine("       FROM containershiprecive cr");
            sb.AppendLine("       LEFT JOIN containercurrentinfo csr ON csr.containerid = cr.containerid");
            sb.AppendLine("       LEFT JOIN container c ON c.containerid = csr.containerid");
            sb.AppendLine("       WHERE 1=1 AND " + containerList + "");
            sb.AppendLine("       GROUP BY csr.familylistnames,csr.productname");
            //外委报废数                    
            sb.AppendLine("       UNION ALL ");
            sb.AppendLine("       SELECT trim(csr.familylistnames) productfamily, csr.productname,SUM(ccm.lossqty) scrapQty");
            sb.AppendLine("       FROM containercommissioning ccm");
            sb.AppendLine("       LEFT JOIN containercurrentinfo csr ON csr.containerid = ccm.containerid");
            sb.AppendLine("       LEFT JOIN container c ON c.containerid = csr.containerid");
            sb.AppendLine("       WHERE 1=1 AND " + containerList + "");
            sb.AppendLine("       GROUP BY csr.familylistnames, csr.productname");
            sb.AppendLine(") M");
            sb.AppendLine("GROUP BY M.productfamily,M.productname");

            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 查询报废数（根据机型、图号、批次分组） add by zhunan on 2017-11-20
        public DataTable GetProductScrapQtyByTogetherflag(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT M.productfamily,M.productname,M.togetherflag,SUM(M.scrapQty) scrapQty");
            sb.AppendLine("FROM (");
            // 加工过程中产生的报废数
            sb.AppendLine("       SELECT TRIM(replace(substr(c.mfgbatch,4),'|','')) productfamily,csr.productname,c.togetherflag,SUM(NVL(csr.lossqty,0)) scrapQty");
            sb.AppendLine("       FROM container c");
            sb.AppendLine("       LEFT JOIN containersteprecord csr ON c.containerid = csr.containerid");
            sb.AppendLine("       WHERE csr.lossqty > 0 AND " + containerList + "");
            sb.AppendLine("       GROUP BY c.mfgbatch, csr.productname,c.togetherflag");
            //转工报废数                      
            sb.AppendLine("       UNION ALL ");
            sb.AppendLine("       SELECT trim(csr.familylistnames) productfamily, csr.productname,csr.togetherflag,SUM(cr.lossqty) scrapQty");
            sb.AppendLine("       FROM containershiprecive cr");
            sb.AppendLine("       LEFT JOIN containercurrentinfo csr ON csr.containerid = cr.containerid");
            sb.AppendLine("       LEFT JOIN container c ON c.containerid = csr.containerid");
            sb.AppendLine("       WHERE 1=1 AND " + containerList + "");
            sb.AppendLine("       GROUP BY csr.familylistnames,csr.productname,csr.togetherflag");
            //外委报废数                    
            sb.AppendLine("       UNION ALL ");
            sb.AppendLine("       SELECT trim(csr.familylistnames) productfamily, csr.productname,csr.togetherflag,SUM(ccm.lossqty) scrapQty");
            sb.AppendLine("       FROM containercommissioning ccm");
            sb.AppendLine("       LEFT JOIN containercurrentinfo csr ON csr.containerid = ccm.containerid");
            sb.AppendLine("       LEFT JOIN container c ON c.containerid = csr.containerid");
            sb.AppendLine("       WHERE 1=1 AND " + containerList + "");
            sb.AppendLine("       GROUP BY csr.familylistnames, csr.productname,csr.togetherflag");
            sb.AppendLine(") M");
            sb.AppendLine("GROUP BY M.productfamily,M.productname,M.togetherflag");

            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 查询在制数（根据机型、图号分组） add by zhunan on 2017-11-20
        public DataTable GetProductWipQty(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT trim(cci.familylistnames) productfamily,cci.productname,SUM(cci.qty) wipQty");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("WHERE cci.isinstore = 0 AND " + containerList + "");
            sb.AppendLine("GROUP BY trim(cci.familylistnames),cci.productname");

            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 查询在制数（根据机型、图号、批次分组） add by zhunan on 2017-11-20
        public DataTable GetProductWipQtyByTogetherflag(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT trim(cci.familylistnames) productfamily,cci.productname,cci.togetherflag,cci.mfgordername,SUM(cci.qty) wipQty");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("WHERE cci.isinstore = 0 AND " + containerList + "");
            sb.AppendLine("GROUP BY trim(cci.familylistnames),cci.productname,cci.togetherflag,cci.mfgordername");

            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 查询待审数（根据机型、图号分组） add by zhunan on 2017-11-20
        public DataTable GetProductDaishenQtyCount(string containerListOld, string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb = new StringBuilder();
            sb.AppendLine("SELECT trim(replace(SUBSTR(c.mfgbatch,4),'|','')) productfamily,cni.matnr productname,sum(cni.zbhgps) daishenQty");
            sb.AppendLine("FROM containernonconforminginfo cni");
            sb.AppendLine("LEFT JOIN container c on c.containerid = cni.containerid");
            sb.AppendLine("WHERE cni.zsljl IS NULL AND " + containerList + "");
            sb.AppendLine("GROUP BY c.mfgbatch,cni.matnr");
            return dal.GetDataTable(sb.ToString()); ;
        }

        // 查询待审数（根据机型、图号、批次分组） add by zhunan on 2017-11-20
        public DataTable GetProductDaishenQtyCountByTogetherflag(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT trim(replace(SUBSTR(c.mfgbatch,4),'|','')) productfamily,cni.matnr productname,c.togetherflag,sum(cni.zbhgps) daishenQty");
            sb.AppendLine("FROM containernonconforminginfo cni");
            sb.AppendLine("LEFT JOIN container c on c.containerid = cni.containerid");
            sb.AppendLine("WHERE cni.zsljl IS NULL AND " + containerList + "");
            sb.AppendLine("GROUP BY c.mfgbatch,cni.matnr,c.togetherflag");
            return dal.GetDataTable(sb.ToString());
        }

        // 查询完工数（根据机型、图号分组） add by zhunan on 2017-11-20
        public DataTable GetProductCompleteQty(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT trim(cci.familylistnames) productfamily,cci.productname,SUM(cci.qty) completeQty");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("WHERE cci.isinstore = 1 AND " + containerList + "");
            sb.AppendLine("GROUP BY cci.familylistnames,cci.productname");

            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 查询完工数（根据机型、图号、批次号分组）add by zhunan on 2017-11-20
        public DataTable GetProductCompleteQtyByTogetherflag(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT trim(cci.familylistnames) productfamily,cci.productname,cci.togetherflag,SUM(cci.qty) completeQty");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("WHERE cci.isinstore = 1 AND " + containerList + "");
            sb.AppendLine("GROUP BY cci.familylistnames,cci.productname,cci.togetherflag");

            DataTable dtResult = dal.GetDataTable(sb.ToString());
            return dtResult;
        }

        // 查询流水卡号 工序完成百分比 add by zhunan on 2017-11-24
        public DataTable GetSpecCompletePercent(string containerList)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT T.containerid,SUM(T.specQty) specQty ");
            sb.AppendLine("FROM ( ");
            //查询外委工序数(批次未入库)
            sb.AppendLine("       SELECT cm.containerid,(ws2.sequence - ws.sequence) specQty");
            sb.AppendLine("       FROM containercommissioning cm");
            sb.AppendLine("       LEFT JOIN containercurrentinfo cci ON cci.containerid = cm.containerid");
            sb.AppendLine("       LEFT JOIN workflowstep ws ON ws.workflowstepid = cm.workflowstepid");
            sb.AppendLine("       LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cm.inworkflowstepid");
            //sb.AppendLine("       WHERE cci.isinstore = 0 AND cm.indate IS NOT NULL AND cci.containerid IN " + containerList + "");
            sb.AppendLine("       WHERE cci.isinstore = 0 AND cm.indate IS NOT NULL AND " + containerList + "");               
            //查询转工工序数       
            sb.AppendLine("       UNION ALL ");                 
            sb.AppendLine("       SELECT M.containerid,SUM(M.specQty) specQty");
            sb.AppendLine("       FROM (");
            sb.AppendLine("             SELECT cci.containerid,cr.containershiprecordid,(MAX(ws.sequence) - MIN(ws.sequence))+1 specQty");
            sb.AppendLine("             FROM containershiprecive cr");
            sb.AppendLine("             LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid");
            sb.AppendLine("             LEFT JOIN containershiprecordstep csd ON csd.containershiprecordid = cr.containershiprecordid");
            sb.AppendLine("             LEFT JOIN workflowstep ws ON csd.workflowstepid = ws.workflowstepid");
            //sb.AppendLine("             WHERE cci.isinstore = 0 AND cr.reciver IS NOT NULL AND cci.containerid IN " + containerList + "");
            sb.AppendLine("             WHERE cci.isinstore = 0 AND cr.reciver IS NOT NULL AND " + containerList + "");
            sb.AppendLine("             GROUP BY cci.containerid,cr.containershiprecordid");
            sb.AppendLine("       ) M ");
            sb.AppendLine("       GROUP BY M.containerid");
            //查询检验工序数（在containersteprecord内排除掉那些在外协过程中产生的报工检验记录）
            sb.AppendLine("       UNION ALL ");                    
            sb.AppendLine("       SELECT csr.containerid,COUNT(DISTINCT csr.workflowstepid) specQty");
            sb.AppendLine("       FROM containercurrentinfo cci");
            sb.AppendLine("       LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid");
            sb.AppendLine("       LEFT JOIN sessionvalues sv ON sv.employeeid = csr.checkemployeeid");
            sb.AppendLine("       WHERE cci.isinstore = 0 AND csr.usecompleted = 1 AND csr.checkemployeeid IS NOT NULL");
            //sb.AppendLine("             AND csr.factoryid = cci.originalfactoryid AND cci.containerid IN " + containerList + "");
            sb.AppendLine("             AND csr.factoryid = cci.originalfactoryid AND " + containerList + "");
            sb.AppendLine("       GROUP BY csr.containerid");
            sb.AppendLine(") T ");
            sb.AppendLine("GROUP BY T.containerid");

            DataTable dtResult = dal.GetDataTable(sb.ToString());

            //查询流水卡工序总数
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containerid,COUNT(ws.workflowid) specTotal");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN workflow wf ON cci.workflowid = wf.workflowid");
            sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowid = wf.workflowid");
            //sb.AppendLine("WHERE cci.isinstore = 0 AND cci.containerid IN " + containerList + "");
            sb.AppendLine("WHERE cci.isinstore = 0 AND " + containerList + "");
            sb.AppendLine("GROUP BY cci.containerid");
            DataTable dtSpecTotal = dal.GetDataTable(sb.ToString());

            
            dtResult.Columns.Add("specTotal",typeof(string));// 工序总数
            dtResult.Columns.Add("specCompleteRate", typeof(string));// 工序完成百分比

            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                string filter = string.Format(" containerid = '{0}'",dtResult.Rows[i]["containerid"]);
                DataRow[] dr = dtSpecTotal.Select(filter);
                if (dr.Length > 0 )
                {
                    decimal specTotal = Convert.ToDecimal(dr[0]["specTotal"].ToString());
                    decimal specCheckedCount = Convert.ToDecimal(dtResult.Rows[i]["specQty"].ToString());
                    if (specTotal != 0)
                    {
                        dtResult.Rows[i]["specCompleteRate"] = ((specCheckedCount / specTotal) * 100).ToString("0.00") + "%";
                    }
                }
            }

            return dtResult;
        }

        // 查询流水卡号 工时完成百分比 add by zhunan on 2017-11-24
        public DataTable GetWorkTimeCompletePercent(string containerList)
        {
            // 完工工时 = 外委工时+转工工时+检验工时
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT T.containerid,SUM(T.workTime) workTime ");
            sb.AppendLine("FROM ( ");
            //查询外委工时(批次未入库)
            sb.AppendLine("       SELECT cm.containerid,SUM(nvl(st.durationperunit,0)) workTime");
            sb.AppendLine("       FROM containercommissioning cm");
            sb.AppendLine("       LEFT JOIN containercurrentinfo cci ON cci.containerid = cm.containerid");
            sb.AppendLine("       LEFT JOIN stepschedulingdetail st on (st.stepid = cm.workflowstepid OR st.stepid = cm.inworkflowstepid)");
            //sb.AppendLine("       WHERE cci.isinstore = 0 AND cm.indate IS NOT NULL AND cci.containerid IN " + containerList + "");
            sb.AppendLine("       WHERE cci.isinstore = 0 AND cm.indate IS NOT NULL AND " + containerList + "");
            sb.AppendLine("       GROUP BY cm.containerid");
            //查询转工工时      
            sb.AppendLine("       UNION ALL ");
            sb.AppendLine("       SELECT M.containerid,SUM(M.workTime) workTime");
            sb.AppendLine("       FROM (");
            sb.AppendLine("             SELECT cci.containerid,cr.containershiprecordid,SUM(nvl(st.durationperunit,0)) workTime");
            sb.AppendLine("             FROM containershiprecive cr");
            sb.AppendLine("             LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid");
            sb.AppendLine("             LEFT JOIN containershiprecordstep csd ON csd.containershiprecordid = cr.containershiprecordid");
            sb.AppendLine("             LEFT JOIN workflowstep ws ON csd.workflowstepid = ws.workflowstepid");
            sb.AppendLine("             LEFT JOIN stepschedulingdetail st on st.stepid = ws.workflowstepid");
            //sb.AppendLine("             WHERE cci.isinstore = 0 AND cr.reciver IS NOT NULL AND cci.containerid IN " + containerList + "");
            sb.AppendLine("             WHERE cci.isinstore = 0 AND cr.reciver IS NOT NULL AND " + containerList + "");
            sb.AppendLine("             GROUP BY cci.containerid,cr.containershiprecordid");
            sb.AppendLine("       ) M ");
            sb.AppendLine("       GROUP BY M.containerid");
            //查询检验工时（在containersteprecord内排除掉那些在外协过程中产生的报工检验记录）
            sb.AppendLine("       UNION ALL ");
            sb.AppendLine("       SELECT M.containerid,SUM(nvl(st.durationperunit,0)) workTime ");
            sb.AppendLine("       FROM (");
            sb.AppendLine("             SELECT DISTINCT csr.containerid,csr.workflowstepid");
            sb.AppendLine("             FROM containercurrentinfo cci");
            sb.AppendLine("             LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid");
            sb.AppendLine("             LEFT JOIN sessionvalues sv ON sv.employeeid = csr.checkemployeeid");
            sb.AppendLine("             WHERE cci.isinstore = 0 AND csr.usecompleted = 1 AND csr.checkemployeeid IS NOT NULL");
            //sb.AppendLine("             AND csr.factoryid = cci.originalfactoryid AND cci.containerid IN " + containerList + "");
            sb.AppendLine("             AND csr.factoryid = cci.originalfactoryid AND " + containerList + "");
            sb.AppendLine("       ) M");
            sb.AppendLine("       LEFT JOIN stepschedulingdetail st on st.stepid = M.workflowstepid");
            sb.AppendLine("       GROUP BY M.containerid");
            sb.AppendLine(") T ");
            sb.AppendLine("GROUP BY T.containerid");

            DataTable dtResult = dal.GetDataTable(sb.ToString());

            // 查询流水卡总工时
            sb = new StringBuilder();
            sb.AppendLine("SELECT cci.containerid,SUM(nvl(st.durationperunit,0)) workTimeAll ");
            sb.AppendLine("FROM containercurrentinfo cci");
            sb.AppendLine("LEFT JOIN workflow wf ON cci.workflowid = wf.workflowid");
            sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowid = wf.workflowid");
            sb.AppendLine("LEFT JOIN stepschedulingdetail st on st.stepid = ws.workflowstepid");
            //sb.AppendLine("WHERE cci.isinstore = 0 AND cci.containerid IN " + containerList + "");
            sb.AppendLine("WHERE cci.isinstore = 0 AND " + containerList + "");
            sb.AppendLine("GROUP BY cci.containerid");

            DataTable dtWorkTimeAll = dal.GetDataTable(sb.ToString());

            dtResult.Columns.Add("workTimeRate",typeof(string)); // 工时完成百分比

            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                string filter = string.Format(" containerid = '{0}'", dtResult.Rows[i]["containerid"]);
                DataRow[] dr = dtWorkTimeAll.Select(filter);
                if (dr.Length > 0)
                {
                    decimal workTimeAll = Convert.ToDecimal(dr[0]["workTimeAll"].ToString());
                    decimal workTimeComplete = Convert.ToDecimal(dtResult.Rows[i]["workTime"].ToString());
                    if (workTimeAll != 0)
                    {
                        dtResult.Rows[i]["workTimeRate"] = ((workTimeComplete / workTimeAll) * 100).ToString("0.00") + "%";
                    }
                }
            }

            return dtResult;
        }

        // 遍历分页之后的查询结果，拼接出字符串作为其他查询的过滤条件 add by zhunan on 2017-11-23
        public string GetContainerIDList(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("(");
            foreach (DataRow dr in dt.Rows)
            {
                if (dr["containerid"].ToString() != "")
                {
                    sb.Append("'" + dr["containerid"] + "',");
                }
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(")");
            return sb.ToString();
        }
              
        // 查询班组  (分厂-工区-班) add Yuxj20171109
        public DataTable GetShift(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT s.shiftid,s.shiftname ");
            sb.AppendLine("FROM shift s");
            sb.AppendLine("LEFT JOIN workcenter w ON w.workcenterid = s.workcenterid");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = w.factoryid");
            sb.AppendLine("WHERE 1=1 ");
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }
            if (para.ContainsKey("shiftname"))
            {
                if (para["shiftname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND s.shiftname = '{0}'", para["shiftname"]);
                }
            }
            if (para.ContainsKey("shiftid"))
            {
                if (para["shiftid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND s.shiftid = '{0}'", para["shiftid"]);
                }
            }
            sb.AppendLine("ORDER BY s.shiftname");
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        #region "装试报表使用"
        // 获取装试组别 Add by Yuxj20181109
        public DataTable GetZSTeam(Dictionary<string, string> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT t.teamid,t.teamname");
            sb.AppendLine("FROM team t");
            sb.AppendLine(" LEFT JOIN shift s ON t.shiftid=s.shiftid ");
            sb.AppendLine("LEFT JOIN workcenter w ON w.workcenterid = s.workcenterid");
            sb.AppendLine("LEFT JOIN factory f ON f.factoryid = w.factoryid");
            sb.AppendLine("WHERE 1=1 ");

            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname = '{0}'", para["factoryname"]);
                }
            }
            if (para.ContainsKey("factoryid"))
            {
                if (para["factoryid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryid = '{0}'", para["factoryid"]);
                }
            }
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername = '{0}'", para["workcentername"]);
                }
            }
            if (para.ContainsKey("workcenterid"))
            {
                if (para["workcenterid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcenterid = '{0}'", para["workcenterid"]);
                }
            }

            if (para.ContainsKey("shiftname"))
            {
                if (para["shiftname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND s.shiftname = '{0}'", para["shiftname"]);
                }
            }

            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname = '{0}'", para["teamname"]);
                }
            }
            if (para.ContainsKey("teamid"))
            {
                if (para["teamid"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamid = '{0}'", para["teamid"]);
                }
            }
            sb.AppendLine("ORDER BY t.teamname");
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }
        /// <summary>
        /// Add by Yuxj20190107
        /// 将DataTable转换为分页Datatable
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable ConvertDataTableToPageDt(DataTable dt, Dictionary<string, object> para)
        {
            int count = Convert.ToInt32(Math.BigMul(Convert.ToInt32(para["currentPage"]) - 1, Convert.ToInt32(para["pageSize"])));
            DataTable _dt = dt.Clone();
            for (int i = count; i <= count + Convert.ToInt32(para["pageSize"]) - 1; i++)
            {
                if (i == dt.Rows.Count)
                {
                    break;
                }
                DataRow _dr = _dt.NewRow();
                _dr.ItemArray = dt.Rows[i].ItemArray;
                _dt.Rows.Add(_dr);
            }
            _dt.AcceptChanges();            
            return _dt;
        }        
        /// <summary>
        /// Add by Yuxj20181214
        /// 获取发动机件号列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetMachineProduct()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT DISTINCT wf.ly2modle_node FROM Workflow wf ");
            sb.AppendLine(" WHERE wf.workflowtype<>'施工工艺' AND wf.ly2modle_node IS NOT NULL ");
            DataTable dt = dal.GetDataTable(sb.ToString());
            DataTable dtRes = new DataTable();
            dtRes.Columns.Add("ProductName");
            dtRes.Columns.Add("ProductNameFamily");
            //需要处理一笔记录中有多个发动机件号(以分号隔开)的情况
            DataTable dtProductFamily = new DataTable();
            foreach (DataRow dr in dt.Rows)
            {
                string strFamily = string.Empty;
                if (dr["ly2modle_node"].ToString().IndexOf(";") >= 0)
                {
                    string[] Ary = dr["ly2modle_node"].ToString().Split(';');
                    for (int i = 0; i <= Ary.Length - 1; i++)
                    {
                        if (dtRes.Select(string.Format("ProductName='{0}'", Ary[i])).Length > 0)
                        {
                            continue;
                        }

                        dtProductFamily = GetProductFamilyByMapping(Ary[i]);
                        if (dtProductFamily.Rows.Count > 0)
                        {
                            strFamily = dtProductFamily.Rows[0]["productfamilyname"].ToString();
                        }
                        else
                            strFamily = string.Empty;

                        dtRes.Rows.Add(Ary[i], Ary[i] + ":" + strFamily);
                    }
                }
                else
                {
                    if (dtRes.Select(string.Format("ProductName='{0}'", dr["ly2modle_node"].ToString())).Length > 0)
                    {
                        continue;
                    }

                    dtProductFamily = GetProductFamilyByMapping(dr["ly2modle_node"].ToString());
                    if (dtProductFamily.Rows.Count > 0)
                    {
                        strFamily = dtProductFamily.Rows[0]["productfamilyname"].ToString();
                    }
                    else
                        strFamily = string.Empty;

                    dtRes.Rows.Add(dr["ly2modle_node"], dr["ly2modle_node"] + ":" + strFamily);
                }

            }

            dtRes.AcceptChanges();
            if (dtRes.Rows.Count > 0)
            {
                dtRes = dtRes.DefaultView.ToTable(true);
                DataView dv = dtRes.DefaultView;
                dv.Sort = "productname asc";
                dtRes = dv.ToTable();
            }

            return dtRes;
        }
        /// <summary>
        /// Add by Yuxj20181227
        /// 获取机型列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetMachineProductFamily()
        {
            DataTable dtProduct = GetMachineProduct();
            DataTable dtFamily = dtProduct.Copy();
            dtFamily.Columns.Add("productfamilyname");
            dtFamily.Columns.Add("productfamilyid");
            foreach (DataRow dr in dtFamily.Rows)
            {
                DataTable df = GetProductFamilyByMapping(dr["productname"].ToString());
                if (df.Rows.Count > 0)
                {
                    dr["productfamilyname"] = df.Rows[0]["productfamilyname"];
                    dr["productfamilyid"] = df.Rows[0]["productfamilyid"];
                }
                else
                {
                    dr["productfamilyname"] = string.Empty;
                    dr["productfamilyid"] = string.Empty;
                }
            }
            if (dtFamily.Rows.Count > 0)
            {
                DataView dv = dtFamily.DefaultView;
                dv.RowFilter = "productfamilyid is not null";
                dtFamily = dv.ToTable(true, "productfamilyname", "productfamilyid");
                dv = dtFamily.DefaultView;
                dv.Sort = " productfamilyname asc";
                dtFamily = dv.ToTable();
            }
            return dtFamily;

        }
        /// <summary>
        /// Add by Yuxj20181227
        /// 获取装试机型
        /// </summary>
        /// <param name="pName"></param>
        /// <returns></returns>
        public DataTable GetProductFamilyByMapping(string pName)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT pf.productfamilyid,pf.productfamilyname,p.description FROM productbase pb   ");
            sb.AppendLine(" LEFT JOIN product p ON pb.revofrcdid=p.productid  ");
            sb.AppendLine("inner JOIN productworkflowmapping pw ON pb.revofrcdid=pw.productid ");
            sb.AppendLine("inner JOIN Workflowproductfamilylist wff ON pw.workflowid=wff.workflowid ");
            sb.AppendLine("inner JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid ");
            sb.AppendFormat(" where pb.productname='{0}' ", pName);
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 获取工步及工序信息
        /// 增加组别工区Modify by Yuxj20190227
        /// </summary>
        /// <param name="workflowid"></param>
        /// <returns></returns>
        public DataTable GetStepAndStepInfoData(string workflowid)
        {
            string sql = @"
                        SELECT wfs.workflowid,wfs.workflowstepname specname,sib.stepinfoname,s.description,wfs.workflowstepid,s.stepinfoid,
                             wfs.workflowstepid || s.stepinfoid stepinfo, wfs.workflowstepname || '--' || s.description workflowstepname,
                             NVL(t.teamname, w.workcentername) teamname,w.workcentername,
                             wfs.sequence || '--'  || wfs.description || '--' || s.description specdesc
                        FROM workflowstep wfs
                        LEFT JOIN team t ON wfs.teamid = t.teamid
                        LEFT JOIN workcenter w ON wfs.workcenterid = w.workcenterid
                        LEFT JOIN stepinfo s ON wfs.workflowstepid = s.workflowstepid
                        LEFT JOIN stepinfobase sib ON sib.stepinfobaseid = s.stepinfobaseid ";
            sql += string.Format(" WHERE wfs.workflowid='{0}'", workflowid);
            sql += " order by wfs.sequence asc";
            DataTable dt = dal.GetDataTable(sql);
            return dt;
        }
        #endregion
    }
}
